﻿namespace Dcms.DAL
{
    using Dcms.BaseLib;
    using Dcms.IDAL;
    using Dcms.Model;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;

    public class Dcms_GuestBookDAL : IDcms_GuestBookDAL
    {
        public Dcms_GuestBookModel AddModel(Dcms_GuestBookModel model)
        {
            string sQLString = "insert into Dcms_GuestBook(GuestBook_CateId,GuestBook_Title,GuestBook_Content,GuestBook_AddTime,GuestBook_UserName,GuestBook_UserIp,GuestBook_UserEmail,GuestBook_UserTel,GuestBook_UserIM,GuestBook_UserPic,GuestBook_ReplyContent,GuestBook_ReplyTime,GuestBook_State,GuestBook_ExFlag1,GuestBook_ExFlag2,GuestBook_ExFlag3,GuestBook_ExFlag4,GuestBook_ExFlag5,GuestBook_ExFlag6,GuestBook_ExFlag7,GuestBook_ExFlag8,GuestBook_ExFlag9,GuestBook_ExFlag10) values(@GuestBook_CateId,@GuestBook_Title,@GuestBook_Content,@GuestBook_AddTime,@GuestBook_UserName,@GuestBook_UserIp,@GuestBook_UserEmail,@GuestBook_UserTel,@GuestBook_UserIM,@GuestBook_UserPic,@GuestBook_ReplyContent,@GuestBook_ReplyTime,@GuestBook_State,@GuestBook_ExFlag1,@GuestBook_ExFlag2,@GuestBook_ExFlag3,@GuestBook_ExFlag4,@GuestBook_ExFlag5,@GuestBook_ExFlag6,@GuestBook_ExFlag7,@GuestBook_ExFlag8,@GuestBook_ExFlag9,@GuestBook_ExFlag10) SELECT @NewId=@@Identity";
            SqlParameter[] cmdParms = new SqlParameter[] { 
                new SqlParameter("@NewId", SqlDbType.Int), new SqlParameter("@GuestBook_CateId", SqlDbType.Int), new SqlParameter("@GuestBook_Title", SqlDbType.VarChar), new SqlParameter("@GuestBook_Content", SqlDbType.Text), new SqlParameter("@GuestBook_AddTime", SqlDbType.DateTime), new SqlParameter("@GuestBook_UserName", SqlDbType.VarChar), new SqlParameter("@GuestBook_UserIp", SqlDbType.VarChar), new SqlParameter("@GuestBook_UserEmail", SqlDbType.VarChar), new SqlParameter("@GuestBook_UserTel", SqlDbType.VarChar), new SqlParameter("@GuestBook_UserIM", SqlDbType.VarChar), new SqlParameter("@GuestBook_UserPic", SqlDbType.VarChar), new SqlParameter("@GuestBook_ReplyContent", SqlDbType.Text), new SqlParameter("@GuestBook_ReplyTime", SqlDbType.DateTime), new SqlParameter("@GuestBook_State", SqlDbType.VarChar), new SqlParameter("@GuestBook_ExFlag1", SqlDbType.VarChar), new SqlParameter("@GuestBook_ExFlag2", SqlDbType.VarChar), 
                new SqlParameter("@GuestBook_ExFlag3", SqlDbType.VarChar), new SqlParameter("@GuestBook_ExFlag4", SqlDbType.VarChar), new SqlParameter("@GuestBook_ExFlag5", SqlDbType.VarChar), new SqlParameter("@GuestBook_ExFlag6", SqlDbType.VarChar), new SqlParameter("@GuestBook_ExFlag7", SqlDbType.VarChar), new SqlParameter("@GuestBook_ExFlag8", SqlDbType.VarChar), new SqlParameter("@GuestBook_ExFlag9", SqlDbType.VarChar), new SqlParameter("@GuestBook_ExFlag10", SqlDbType.VarChar)
             };
            cmdParms[0].Direction = ParameterDirection.Output;
            cmdParms[1].Value = model.GuestBook_CateId;
            cmdParms[2].Value = model.GuestBook_Title;
            cmdParms[3].Value = model.GuestBook_Content;
            cmdParms[4].Value = model.GuestBook_AddTime;
            cmdParms[5].Value = model.GuestBook_UserName;
            cmdParms[6].Value = model.GuestBook_UserIp;
            cmdParms[7].Value = model.GuestBook_UserEmail;
            cmdParms[8].Value = model.GuestBook_UserTel;
            cmdParms[9].Value = model.GuestBook_UserIM;
            cmdParms[10].Value = model.GuestBook_UserPic;
            cmdParms[11].Value = model.GuestBook_ReplyContent;
            cmdParms[12].Value = model.GuestBook_ReplyTime;
            cmdParms[13].Value = model.GuestBook_State;
            cmdParms[14].Value = model.GuestBook_ExFlag1;
            cmdParms[15].Value = model.GuestBook_ExFlag2;
            cmdParms[0x10].Value = model.GuestBook_ExFlag3;
            cmdParms[0x11].Value = model.GuestBook_ExFlag4;
            cmdParms[0x12].Value = model.GuestBook_ExFlag5;
            cmdParms[0x13].Value = model.GuestBook_ExFlag6;
            cmdParms[20].Value = model.GuestBook_ExFlag7;
            cmdParms[0x15].Value = model.GuestBook_ExFlag8;
            cmdParms[0x16].Value = model.GuestBook_ExFlag9;
            cmdParms[0x17].Value = model.GuestBook_ExFlag10;
            SqlHelper.ExecuteSql(sQLString, cmdParms);
            model.GuestBook_Id = (int) cmdParms[0].Value;
            return model;
        }

        public IList<Dcms_GuestBookModel> DataSetToList(DataSet ds)
        {
            IList<Dcms_GuestBookModel> list = new List<Dcms_GuestBookModel>();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                Dcms_GuestBookModel item = new Dcms_GuestBookModel();
                if (ds.Tables[0].Rows[i]["GuestBook_Id"].ToString() != "") item.GuestBook_Id = int.Parse(ds.Tables[0].Rows[i]["GuestBook_Id"].ToString());
                if (ds.Tables[0].Rows[i]["GuestBook_CateId"].ToString() != "") item.GuestBook_CateId = int.Parse(ds.Tables[0].Rows[i]["GuestBook_CateId"].ToString());
                item.GuestBook_Title = ds.Tables[0].Rows[i]["GuestBook_Title"].ToString();
                item.GuestBook_Content = ds.Tables[0].Rows[i]["GuestBook_Content"].ToString();
                if (ds.Tables[0].Rows[i]["GuestBook_AddTime"].ToString() != "") item.GuestBook_AddTime = DateTime.Parse(ds.Tables[0].Rows[i]["GuestBook_AddTime"].ToString());
                item.GuestBook_UserName = ds.Tables[0].Rows[i]["GuestBook_UserName"].ToString();
                item.GuestBook_UserIp = ds.Tables[0].Rows[i]["GuestBook_UserIp"].ToString();
                item.GuestBook_UserEmail = ds.Tables[0].Rows[i]["GuestBook_UserEmail"].ToString();
                item.GuestBook_UserTel = ds.Tables[0].Rows[i]["GuestBook_UserTel"].ToString();
                item.GuestBook_UserIM = ds.Tables[0].Rows[i]["GuestBook_UserIM"].ToString();
                item.GuestBook_UserPic = ds.Tables[0].Rows[i]["GuestBook_UserPic"].ToString();
                item.GuestBook_ReplyContent = ds.Tables[0].Rows[i]["GuestBook_ReplyContent"].ToString();
                if (ds.Tables[0].Rows[i]["GuestBook_ReplyTime"].ToString() != "") item.GuestBook_ReplyTime = DateTime.Parse(ds.Tables[0].Rows[i]["GuestBook_ReplyTime"].ToString());
                item.GuestBook_State = ds.Tables[0].Rows[i]["GuestBook_State"].ToString();
                item.GuestBook_ExFlag1 = ds.Tables[0].Rows[i]["GuestBook_ExFlag1"].ToString();
                item.GuestBook_ExFlag2 = ds.Tables[0].Rows[i]["GuestBook_ExFlag2"].ToString();
                item.GuestBook_ExFlag3 = ds.Tables[0].Rows[i]["GuestBook_ExFlag3"].ToString();
                item.GuestBook_ExFlag4 = ds.Tables[0].Rows[i]["GuestBook_ExFlag4"].ToString();
                item.GuestBook_ExFlag5 = ds.Tables[0].Rows[i]["GuestBook_ExFlag5"].ToString();
                item.GuestBook_ExFlag6 = ds.Tables[0].Rows[i]["GuestBook_ExFlag6"].ToString();
                item.GuestBook_ExFlag7 = ds.Tables[0].Rows[i]["GuestBook_ExFlag7"].ToString();
                item.GuestBook_ExFlag8 = ds.Tables[0].Rows[i]["GuestBook_ExFlag8"].ToString();
                item.GuestBook_ExFlag9 = ds.Tables[0].Rows[i]["GuestBook_ExFlag9"].ToString();
                item.GuestBook_ExFlag10 = ds.Tables[0].Rows[i]["GuestBook_ExFlag10"].ToString();
                list.Add(item);
            }
            return list;
        }

        public bool DeleteAllCheck(string AllID)
        {
            return SqlHelper.ExecuteSql("delete from Dcms_GuestBook where GuestBook_Id In (" + AllID + ")", null) > 0;
        }

        public void DeleteModelByID(int ID)
        {
            string sQLString = "delete from Dcms_GuestBook where GuestBook_Id=@GuestBook_Id";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@GuestBook_Id", SqlDbType.Int) };
            cmdParms[0].Value = ID;
            SqlHelper.ExecuteSql(sQLString, cmdParms);
        }

        public string GetDataCount(string SqlStr)
        {
            return SqlHelper.GetNoteCount(SqlStr).ToString();
        }

        public IList<Dcms_GuestBookModel> GetDataListByPage(string SqlStr, int CurPage, int PageSize)
        {
            return this.DataSetToList(SqlHelper.CreateDataSet(SqlStr, (CurPage - 1) * PageSize, PageSize));
        }

        public Dcms_GuestBookModel GetModelByID(int ID)
        {
            string sQLString = "select * from Dcms_GuestBook where GuestBook_Id=@GuestBook_Id";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@GuestBook_Id", SqlDbType.Int) };
            cmdParms[0].Value = ID;
            Dcms_GuestBookModel model = new Dcms_GuestBookModel();
            DataSet ds = SqlHelper.CreateDataSet(sQLString, cmdParms);
            IList<Dcms_GuestBookModel> list = this.DataSetToList(ds);
            return ((list.Count > 0) ? list[0] : null);
        }

        public DataSet GetModelList()
        {
            string sql = "select * from Dcms_GuestBook";
            return SqlHelper.CreateDataSet(sql);
        }

        public Dcms_GuestBookModel GetNewModelByCatID(int CatID)
        {
            return null;
        }

        public Dcms_GuestBookModel Update(Dcms_GuestBookModel model)
        {
            string sQLString = "update Dcms_GuestBook set GuestBook_CateId=@GuestBook_CateId,GuestBook_Title=@GuestBook_Title,GuestBook_Content=@GuestBook_Content,GuestBook_AddTime=@GuestBook_AddTime,GuestBook_UserName=@GuestBook_UserName,GuestBook_UserIp=@GuestBook_UserIp,GuestBook_UserEmail=@GuestBook_UserEmail,GuestBook_UserTel=@GuestBook_UserTel,GuestBook_UserIM=@GuestBook_UserIM,GuestBook_UserPic=@GuestBook_UserPic,GuestBook_ReplyContent=@GuestBook_ReplyContent,GuestBook_ReplyTime=@GuestBook_ReplyTime,GuestBook_State=@GuestBook_State,GuestBook_ExFlag1=@GuestBook_ExFlag1,GuestBook_ExFlag2=@GuestBook_ExFlag2,GuestBook_ExFlag3=@GuestBook_ExFlag3,GuestBook_ExFlag4=@GuestBook_ExFlag4,GuestBook_ExFlag5=@GuestBook_ExFlag5,GuestBook_ExFlag6=@GuestBook_ExFlag6,GuestBook_ExFlag7=@GuestBook_ExFlag7,GuestBook_ExFlag8=@GuestBook_ExFlag8,GuestBook_ExFlag9=@GuestBook_ExFlag9,GuestBook_ExFlag10=@GuestBook_ExFlag10 where GuestBook_Id=@GuestBook_Id";
            SqlParameter[] cmdParms = new SqlParameter[] { 
                new SqlParameter("@GuestBook_CateId", SqlDbType.Int), new SqlParameter("@GuestBook_Title", SqlDbType.VarChar), new SqlParameter("@GuestBook_Content", SqlDbType.Text), new SqlParameter("@GuestBook_AddTime", SqlDbType.DateTime), new SqlParameter("@GuestBook_UserName", SqlDbType.VarChar), new SqlParameter("@GuestBook_UserIp", SqlDbType.VarChar), new SqlParameter("@GuestBook_UserEmail", SqlDbType.VarChar), new SqlParameter("@GuestBook_UserTel", SqlDbType.VarChar), new SqlParameter("@GuestBook_UserIM", SqlDbType.VarChar), new SqlParameter("@GuestBook_UserPic", SqlDbType.VarChar), new SqlParameter("@GuestBook_ReplyContent", SqlDbType.Text), new SqlParameter("@GuestBook_ReplyTime", SqlDbType.DateTime), new SqlParameter("@GuestBook_State", SqlDbType.VarChar), new SqlParameter("@GuestBook_ExFlag1", SqlDbType.VarChar), new SqlParameter("@GuestBook_ExFlag2", SqlDbType.VarChar), new SqlParameter("@GuestBook_ExFlag3", SqlDbType.VarChar), 
                new SqlParameter("@GuestBook_ExFlag4", SqlDbType.VarChar), new SqlParameter("@GuestBook_ExFlag5", SqlDbType.VarChar), new SqlParameter("@GuestBook_ExFlag6", SqlDbType.VarChar), new SqlParameter("@GuestBook_ExFlag7", SqlDbType.VarChar), new SqlParameter("@GuestBook_ExFlag8", SqlDbType.VarChar), new SqlParameter("@GuestBook_ExFlag9", SqlDbType.VarChar), new SqlParameter("@GuestBook_ExFlag10", SqlDbType.VarChar), new SqlParameter("@GuestBook_Id", SqlDbType.Int)
             };
            cmdParms[0].Value = model.GuestBook_CateId;
            cmdParms[1].Value = model.GuestBook_Title;
            cmdParms[2].Value = model.GuestBook_Content;
            cmdParms[3].Value = model.GuestBook_AddTime;
            cmdParms[4].Value = model.GuestBook_UserName;
            cmdParms[5].Value = model.GuestBook_UserIp;
            cmdParms[6].Value = model.GuestBook_UserEmail;
            cmdParms[7].Value = model.GuestBook_UserTel;
            cmdParms[8].Value = model.GuestBook_UserIM;
            cmdParms[9].Value = model.GuestBook_UserPic;
            cmdParms[10].Value = model.GuestBook_ReplyContent;
            cmdParms[11].Value = model.GuestBook_ReplyTime;
            cmdParms[12].Value = model.GuestBook_State;
            cmdParms[13].Value = model.GuestBook_ExFlag1;
            cmdParms[14].Value = model.GuestBook_ExFlag2;
            cmdParms[15].Value = model.GuestBook_ExFlag3;
            cmdParms[0x10].Value = model.GuestBook_ExFlag4;
            cmdParms[0x11].Value = model.GuestBook_ExFlag5;
            cmdParms[0x12].Value = model.GuestBook_ExFlag6;
            cmdParms[0x13].Value = model.GuestBook_ExFlag7;
            cmdParms[20].Value = model.GuestBook_ExFlag8;
            cmdParms[0x15].Value = model.GuestBook_ExFlag9;
            cmdParms[0x16].Value = model.GuestBook_ExFlag10;
            cmdParms[0x17].Value = model.GuestBook_Id;
            SqlHelper.ExecuteSql(sQLString, cmdParms);
            return model;
        }

        public bool UpdateState(string AllID, int StateValue)
        {
            return SqlHelper.ExecuteSql("Update Dcms_GuestBook Set GuestBook_State='" + StateValue.ToString() + "' where GuestBook_Id In (" + AllID + ")", null) > 0;
        }
    }
}

